/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package mnstudent.dataacesslayer;

import java.sql.*;

/**
 *
 * @author lee
 */
public class DataExecuteUseStoreProc {
    
    //<editor-fold defaultstate="collapsed" desc="Attributes">
    
    private String m_sqlCmd;
    private CallableStatement m_callStatement;
    private DataConnection m_dtConnect;
    //</editor-fold>
    //Use with StoreProcedure
    //<editor-fold defaultstate="collapsed" desc="Constructor">
    public DataExecuteUseStoreProc()
    {
        this.m_dtConnect = new DataConnection();
        this.m_callStatement = null;
        this.m_sqlCmd = "";
    }
    
    //</editor-fold>
    
    //<editor-fold defaultstate="collapsed" desc="Method">
    /**
     * Get all data of table from database
     * @param _sqlCmd
     * @return 
     */
    public ResultSet getDataFromDatabase(String _sqlCmd, Object[] _arrValue){
        this.m_sqlCmd = _sqlCmd;
        if(this.m_dtConnect.openConection())
        {
            try
            {
                this.m_callStatement = this.m_dtConnect.getConnection().prepareCall(m_sqlCmd);
                if(_arrValue != null){
                    for (int i = 0; i < _arrValue.length; i++) {
                        this.m_callStatement.setObject(i + 1, _arrValue[i]);
                    }
                }
                return this.m_callStatement.executeQuery();
            }
            catch(SQLException ex)
            {
                this.m_dtConnect.closeConnection();
                return null;
            }
        }
        else
        {
            return null;
        }
    }
    
    public int updateDataToDatabase(String _sqlCmd, Object[] _arrValue){
        this.m_sqlCmd = _sqlCmd;
        if(this.m_dtConnect.openConection())
        {
            int _rowsAffected = 0;

                try
                {  
                    this.m_dtConnect.getConnection().setAutoCommit(true);
                    if(_arrValue != null){
                        for (int i = 0; i < _arrValue.length; i++) {
                            this.m_callStatement.setObject(i + 1, _arrValue[i]);
                        }
                }
                    this.m_callStatement = this.m_dtConnect.getConnection().prepareCall(_sqlCmd);
                    _rowsAffected = this.m_callStatement.executeUpdate();
                     this.m_dtConnect.getConnection().commit();
                }
                catch(SQLException ex)
                {
                    this.m_dtConnect.getConnection().rollback();
                    _rowsAffected = 0;
                }
                finally
                {
                    this.m_dtConnect.closeConnection();
                    return _rowsAffected;
                }
        }
        else
        {
            return 0;
        }
    }
    
    public int[] updateMoreDataToDatabase(String SqlCmd, int _countParameter, Object[] _arrValue)
    {
        this.m_sqlCmd = SqlCmd;
        if(this.m_dtConnect.openConection())
        {
            
            int[] updateCounts = null;
            try 
            {
                this.m_dtConnect.getConnection().setAutoCommit(true);
                this.m_callStatement = m_dtConnect.getConnection().prepareCall(SqlCmd);
                int _index = 0;
                for(int i = 0; i<(_arrValue.length/_countParameter); ++i){
                    
                    for(int j = 1; j <= _countParameter; ++j){
                        this.m_callStatement.setObject(j+1, _arrValue[_index]);
                        ++_index;
                    }
                    this.m_callStatement.addBatch();
                    
                }
                updateCounts = this.m_callStatement.executeBatch();
                this.m_dtConnect.getConnection().commit();
            } 
            catch (SQLException ex) 
            {
                //Don't execute
                this.m_dtConnect.getConnection().rollback();
                updateCounts = null;
            }
            finally
            {
                this.m_dtConnect.closeConnection();
                return updateCounts;
            }
        }
        else
        {
            return new int[]{'\0'};
        }
    }
    
    public boolean updateMoreTableToDatabase(CallableStatement[] _arrCallStatement)
    {
        if(this.m_dtConnect.openConection())
        {
            boolean _result = false;
            try
            {
                this.m_dtConnect.getConnection().setAutoCommit(true);
                try
                {
                    for(CallableStatement item : _arrCallStatement)
                    {
                        item.executeUpdate();
                    }
                    _result =  true;
                }
                catch(SQLException ex)
                {
                    for(CallableStatement item : _arrCallStatement)
                    {
                        item.close();
                    }
                }
                this.m_dtConnect.getConnection().commit();
           }
            catch(SQLException ex)
            {
                try 
                {
                    this.m_dtConnect.getConnection().rollback();
                }
                catch (SQLException ex1) 
                {
                }
            }
            finally
            {
                this.m_dtConnect.closeConnection();
                return _result;
            }
        }
        else
        {
            return false;
        }
    }
    //</editor-fold>
}
